import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

import net.sf.json.JSON;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

@WebServlet("/Student")
public class Student extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	// JDBC 驱动名及数据库 URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	static final String DB_URL = "jdbc:mysql://localhost:3306/Student_imf?characterEncoding=utf-8";
	//不加？后面的语句，则数据传入数据库的时候可能会出现乱码

	// 数据库的用户名与密码(需要根据自己的设置)
	static final String USER = "root";
	static final String PASS = "root";
       
    public Student() {
        super();
      
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, 
	IOException {
		
	    request.setCharacterEncoding("UTF-8");
	    response.setCharacterEncoding("UTF-8");
			  
	    Connection conn = null;
	    Statement stmt = null;
	    PreparedStatement st;
	    try{
	            // 注册 JDBC 驱动
	            Class.forName("com.mysql.jdbc.Driver");
	        
	            // 打开链接
	            conn = (Connection) DriverManager.getConnection(DB_URL,USER,PASS);
	        
	            // 执行查询
	            stmt = (Statement) conn.createStatement();
	          
	            String sql;
	            sql = "SELECT id,number,nm,college,major,grade,cls,age FROM Student_imf";
	            ResultSet rs = stmt.executeQuery(sql);
	        
	            // 展开结果集数据库
	            while(rs.next()){
	                // 通过字段检索
	                String id  = rs.getString("id");
	                String number = rs.getString("number");
	                String nm = rs.getString("nm");
	                String college = rs.getString("college");
	                String major = rs.getString("major");
	                String grade = rs.getString("grade");
	                String cls = rs.getString("cls");
	                String age = rs.getString("age");
	                String str=id+" "+number+" "+nm+" "+college+" "+major+" "+grade+" "+cls+" "+age;	
	                response.getWriter().println(str);//一次读一个对象，传到前台
	               }
	            /*当进行完增、删、改传数据回来的时候加一个参数，
	             * 以便判断进行的是什么操作以及要对那个对象的数据进行改变，
	             * 如果是查看的话就不用对数据进行改变
	            */
	            
	          String key=request.getParameter("key");//用于判断是进行增或者删还是改的操作
	           
	          if(key==null)//如果不判断将会出现空指针错误
	        	  ;
	          else
	          {
	            if(key.equals("1"))//增
	            {
	            	
	            	String number=request.getParameter("number2");//学号
	            	String nm=request.getParameter("name");
	            	String c=request.getParameter("college");
	            	String m=request.getParameter("major");
	            	String g=request.getParameter("grade");
	            	String cl=request.getParameter("cls");
	            	String a=request.getParameter("age");
	            	
	            	sql="INSERT INTO Student_imf(number,nm,college,major,grade,cls,age)"
	            			+ " VALUES('"+number+"','"+ nm+"','"+c+"','"+
	            			                                m+"','"+g +"','"+ cl+"','"+a+"')";
	                st = (PreparedStatement) conn.prepareStatement(sql);
	                st.executeUpdate();
	                st.close();
	            }
	            
	            if(key.equals("2"))//删
	            {	
	            	String n1=request.getParameter("number2");//(通过学生的序学号来删除学生信息)
	            	
	            	//全部删除
	            	if(n1.equals("0")) {
	          		  sql="DELETE FROM Student_imf ";
	          		  st = (PreparedStatement) conn.prepareStatement(sql);
	                  st.executeUpdate();
	                  st.close();
	          		}
	            	
	            	//单个删除
	          		else
	          		{
	          		    sql="DELETE FROM Student_imf WHERE number=?";
	          		    st = (PreparedStatement) conn.prepareStatement(sql);
	          		    st.setString(1,n1);//相当于用n1的值去代替第一个？
	                    st.executeUpdate();
	                    st.close();
	          		}	            	
	            }
	            if(key.equals("3"))//改
	            {
            	    String n2=request.getParameter("number2");
            	    String nm=request.getParameter("name");
            	    String c=request.getParameter("college");
            	    String m=request.getParameter("major");
            	    String g=request.getParameter("grade");
            	    String cl=request.getParameter("cls");
            	    String a=request.getParameter("age");
            	  
            	    sql="UPDATE Student_imf SET number=?,nm=?,college=?,"
	                     + "major=?,grade=?,cls=?,age=? where number=?";	
               
            	    st = (PreparedStatement) conn.prepareStatement(sql);
            	    st.setString(1, n2);
            	    st.setString(2, nm);
            	    st.setString(3, c);
            	    st.setString(4, m);
            	    st.setString(5, g);
            	    st.setString(6, cl);
            	    st.setString(7, a);
            	    st.setString(8, n2);//通过学号来修改
            	    
                    st.executeUpdate();
                    st.close();
	            }
	         }
	            
	         // 完成后关闭
	         rs.close();
	         stmt.close();
	         conn.close();
	      }
	      catch(SQLException se){
	           // 处理 JDBC 错误
	           se.printStackTrace();
	      }
	      catch(Exception e){
	          // 处理 Class.forName 错误
	           e.printStackTrace();
	      }
	      finally{
	           // 关闭资源
	           try{
	               if(stmt!=null) stmt.close();
	           }
	           catch(SQLException se2){
	           }// 什么都不做
	           try{
	               if(conn!=null) conn.close();
	           }
	           catch(SQLException se){
	               se.printStackTrace();
	           }
	        } 
	    }
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {
	    	doGet(request, response);
	 }
}
